package repository

import (
	"context"
	"database/sql"
	"regexp"
	"time"

	"gitee.com/hp-huiw/family-expense-api/internal/constant"
	"gitee.com/hp-huiw/family-expense-api/internal/db"
	dbmodel "gitee.com/hp-huiw/family-expense-api/internal/db/model"
	"gitee.com/hp-huiw/my-go-core/util"
	jet "github.com/go-jet/jet/v2/mysql"
)

type UserBasis struct {
	ID        string
	Username  string
	FullName  string
	AvatarUrl string
	Enabled   *bool
}

type IncomeExpenseDetail struct {
	ID                   string     `db:"id"`
	IncomeExpenseDate    *time.Time `db:"income_expense_date"`
	Amount               float64    `db:"amount"`
	Type                 string     `db:"type"`
	DetailType           string     `db:"detail_type"`
	PaymentType          string     `db:"payment_type"`
	BankID               *string    `db:"bank_id"`
	BankShortName        *string    `db:"bank_short_name"`
	BankCardID           *string    `db:"bank_card_id"`
	BankCardName         *string    `db:"bank_card_name"`
	BankCardEnabled      *bool      `db:"bank_card_enabled"`
	BankCardIsCreditCard *bool      `db:"bank_card_is_credit_card"`
	Details              string     `db:"details"`
	Remark               *string    `db:"remark"`
	Latitude             *float64   `db:"latitude"`
	Longitude            *float64   `db:"longitude"`
	FormattedAddress     *string    `db:"formatted_address"`
	Address              *string    `db:"address"`
	Province             *string    `db:"province"`
	City                 *string    `db:"city"`
	District             *string    `db:"district"`
	CreatedByStr         *string    `db:"created_by_str"`
	UpdatedByStr         *string    `db:"updated_by_str"`
	DateCreated          *time.Time `db:"date_created"`
	LastUpdated          *time.Time `db:"last_updated"`
	CreatedBy            *UserBasis
	UpdatedBy            *UserBasis
}

type DetailTypeSummary struct {
	DetailType string  `db:"detail_type"`
	Amount     float64 `db:"amount"`
}

type AmountSummary struct {
	Amount      float64 `db:"amount"`
	PaymentType string  `db:"payment_type"`
	Type        string  `db:"type"`
}

type DatedAmountSummary struct {
	IncomeExpenseDate time.Time `db:"income_expenseDate"`
	Amount            float64   `db:"amount"`
	PaymentType       string    `db:"payment_type"`
	Type              string    `db:"type"`
}

type OverallSummary struct {
	Year        int     `db:"year"`
	Type        string  `db:"type"`
	PaymentType string  `db:"payment_type"`
	Amount      float64 `db:"amount"`
}

type PeriodAmount struct {
	Period int     `db:"period"`
	Amount float64 `db:"amount"`
}

type FormattedAddressCoordinate struct {
	Latitude         *float64 `db:"latitude"`
	Longitude        *float64 `db:"longitude"`
	FormattedAddress *string  `db:"formatted_address"`
}

type IncomeExpenseDetailQueryArgument struct {
	PageSize                int64
	PageIndex               int64
	AccountGroupID          string
	Keyword                 string
	KeywordsSearchCondition string
	Type                    string
	DetailType              string
	PaymentType             string
	BankCardID              string
	// StartDateStr            string
	// EndDateStr              string
	StartDate *time.Time
	EndDate   *time.Time
}

type IncomeExpenseDetailRepository interface {
	Exists(ctx context.Context, accountGroupID string, bankCardID string) (bool, error)
	DetailTypeExists(ctx context.Context, accountGroupID string, incomeExpenseType string, detailType string) (bool, error)
	Query(ctx context.Context, queryArgument *IncomeExpenseDetailQueryArgument) ([]IncomeExpenseDetail, error)
	QueryTotalAmount(ctx context.Context, queryArgument *IncomeExpenseDetailQueryArgument) (float64, error)
	QueryUsedBankCardIDs(ctx context.Context, bankCardIDs []string, accountGroupID string) ([]string, error)
	QueryUsedDetailTypeNames(ctx context.Context, incomeExpenseType string, detailTypeNames []string, accountGroupID string) ([]string, error)
	QueryDetails(ctx context.Context, accountGroupID string, id string) (*IncomeExpenseDetail, error)
	QueryCoordinates(ctx context.Context, accountGroupID string, year string) ([]FormattedAddressCoordinate, error)
	QueryTotalIncomeExpenseCount(ctx context.Context, accountGroupID string) (int64, error)
	QueryTotalIncomeExpenseDays(ctx context.Context, accountGroupID string) (int64, error)
	QueryMonthlySummaryList(ctx context.Context, accountGroupID string, date time.Time) ([]AmountSummary, error)
	QueryDailySummaryList(ctx context.Context, accountGroupID string, uniqueDates []time.Time) ([]DatedAmountSummary, error)
	QueryExistingDistinctYears(ctx context.Context, accountGroupID string) ([]int, error)
	QueryTopRank(ctx context.Context, accountGroupID string, pageSize int64, pageIndex int64, incomeExpenseType string, paymentType string, year int, month int) ([]IncomeExpenseDetail, error)
	QueryOverallSummary(ctx context.Context, accountGroupID string) ([]OverallSummary, error)
	QueryAnnualSummary(ctx context.Context, accountGroupID string, years []int) ([]OverallSummary, error)
	QuerySummaryByDetailType(ctx context.Context, accountGroupID string, paymentType string, year int, month int, incomeExpenseType string, detailTypes []string) ([]DetailTypeSummary, error)
	QueryPeriodSummary(ctx context.Context, accountGroupID string, incomeExpenseType string, paymentType string, year int, month int) ([]PeriodAmount, error)
	Add(ctx context.Context, data *dbmodel.IncomeExpenseDetail) error
	Update(ctx context.Context, accountGroupID string, model *dbmodel.IncomeExpenseDetail) error
	UpdateDetailTypeWithTx(ctx context.Context, tx *sql.Tx, accountGroupID string, incomeExpenseType string, currentDetailType string, newDetailType string) error
	BatchDelete(ctx context.Context, accountGroupID string, ids []string) error
	DeleteWithTx(ctx context.Context, tx *sql.Tx, accountGroupID string) error
}

func NewIncomeExpenseDetailRepository(db *sql.DB) IncomeExpenseDetailRepository {
	return &incomeExpenseDetailRepository{db: db}
}

// Exists checks if this bank card is used by income/expense.
func (r *incomeExpenseDetailRepository) Exists(ctx context.Context, accountGroupID string, bankCardID string) (bool, error) {
	condition := tbDetail.AccountGroupID.EQ(jet.String(accountGroupID)).
		AND(tbDetail.BankCardID.EQ(jet.String(bankCardID)))

	return db.Exists(ctx, r.db, tbDetail, condition)
}

// DetailTypeExists checks if this detail type is used by income/expense.
func (r *incomeExpenseDetailRepository) DetailTypeExists(ctx context.Context, accountGroupID string, incomeExpenseType string, detailType string) (bool, error) {
	condition := tbDetail.AccountGroupID.EQ(jet.String(accountGroupID)).
		AND(tbDetail.Type.EQ(jet.String(incomeExpenseType))).
		AND(tbDetail.DetailType.EQ(jet.String(detailType)))

	return db.Exists(ctx, r.db, tbDetail, condition)
}

// Query queries all income/expense which associated with provided account group.
func (r *incomeExpenseDetailRepository) Query(ctx context.Context, queryArgument *IncomeExpenseDetailQueryArgument) ([]IncomeExpenseDetail, error) {
	var query []struct {
		ID                   string     `db:"id"`
		IncomeExpenseDate    *time.Time `db:"income_expense_date"`
		Amount               float64    `db:"amount"`
		Type                 string     `db:"type"`
		DetailType           string     `db:"detail_type"`
		PaymentType          string     `db:"payment_type"`
		BankID               *string    `db:"bank_id"`
		BankShortName        *string    `db:"bank_short_name"`
		BankCardID           *string    `db:"bank_card_id"`
		BankCardName         *string    `db:"bank_card_name"`
		BankCardEnabled      *bool      `db:"bank_card_enabled"`
		BankCardIsCreditCard *bool      `db:"bank_card_is_credit_card"`
		Details              string     `db:"details"`
		Remark               *string    `db:"remark"`
		Latitude             *float64   `db:"latitude"`
		Longitude            *float64   `db:"longitude"`
		FormattedAddress     *string    `db:"formatted_address"`
		Address              *string    `db:"address"`
		Province             *string    `db:"province"`
		City                 *string    `db:"city"`
		District             *string    `db:"district"`
		CreatedByStr         *string    `db:"created_by_str"`
		UpdatedByStr         *string    `db:"updated_by_str"`
		DateCreated          *time.Time `db:"date_created"`
		LastUpdated          *time.Time `db:"last_updated"`
		CreatedBy            *UserBasis
		UpdatedBy            *UserBasis
	}

	condition := r.getSharedCondition(queryArgument)

	stmt := jet.
		SELECT(
			tbDetail.ID.AS("id"),
			tbDetail.IncomeExpenseDate.AS("income_expense_date"),
			tbDetail.Amount.AS("amount"),
			tbDetail.Type.AS("type"),
			tbDetail.DetailType.AS("detail_type"),
			tbDetail.PaymentType.AS("payment_type"),
			tbBank.ID.AS("bank_id"),
			tbBank.ShortName.AS("bank_short_name"),
			tbBankCard.ID.AS("bank_card_id"),
			tbBankCard.Name.AS("bank_card_name"),
			tbBankCard.IsCreditCard.AS("bank_card_is_credit_card"),
			tbDetail.Details.AS("details"),
			tbDetail.CreatedBy.AS("created_by_str"),
			tbDetail.UpdatedBy.AS("updated_by_str"),
		).
		FROM(
			tbDetail.
				LEFT_JOIN(tbBankCard, tbBankCard.ID.EQ(tbDetail.BankCardID)).
				LEFT_JOIN(tbBank, tbBank.ID.EQ(tbBankCard.BankID)),
		).
		WHERE(
			condition,
		).
		ORDER_BY(
			tbDetail.IncomeExpenseDate.DESC(),
			tbDetail.Type.DESC(),
			tbDetail.BankCardID.ASC(),
			tbDetail.LastUpdated.DESC(),
		).
		LIMIT(queryArgument.PageSize).
		OFFSET(queryArgument.PageSize * queryArgument.PageIndex)

	if err := stmt.QueryContext(ctx, r.db, &query); err != nil {
		return nil, err
	}

	result := make([]IncomeExpenseDetail, 0, len(query))

	for _, item := range query {
		result = append(result, item)
	}

	return result, nil
}

// QueryTotalAmount queries all income/expense total amount which associated with provided account group.
func (r *incomeExpenseDetailRepository) QueryTotalAmount(ctx context.Context, queryArgument *IncomeExpenseDetailQueryArgument) (float64, error) {
	var query struct {
		TotalAmount float64 `db:"total_amount"`
	}

	condition := r.getSharedCondition(queryArgument)

	stmt := jet.
		SELECT(
			jet.NULLIF(jet.SUMf(tbDetail.Amount), jet.Int(0)).AS("total_amount"),
		).
		FROM(
			tbDetail,
		).
		WHERE(
			condition,
		)

	err := stmt.QueryContext(ctx, r.db, &query)

	if err != nil {
		return -1, err
	}

	return query.TotalAmount, nil
}

// QueryUsedBankCardIDs queries which bank card id is used by income/expense based on provided bank card id list.
// return The string list of bank card id which is used by income/expense. Any unused bank card id will be filtered.
func (r *incomeExpenseDetailRepository) QueryUsedBankCardIDs(ctx context.Context, bankCardIDs []string, accountGroupID string) ([]string, error) {
	query := []string{}

	if len(bankCardIDs) > 0 {
		bankCardIDsExpression := db.SliceExpression(bankCardIDs)

		stmt := jet.
			SELECT(
				tbDetail.BankCardID,
			).
			FROM(
				tbDetail,
			).
			WHERE(
				tbDetail.AccountGroupID.EQ(jet.String(accountGroupID)).
					AND(tbDetail.BankCardID.IN(bankCardIDsExpression...)),
			)

		if err := stmt.QueryContext(ctx, r.db, &query); err != nil {
			return nil, err
		}
	}

	return query, nil
}

// QueryUsedDetailTypeNames queries which detail type is used by income/expense based on provided income/expense type and detail type name list.
// return The string list of detail type name which is used by income/expense. Any unused detail type will be filtered.
func (r *incomeExpenseDetailRepository) QueryUsedDetailTypeNames(ctx context.Context, incomeExpenseType string, detailTypeNames []string, accountGroupID string) ([]string, error) {
	query := []string{}

	if len(detailTypeNames) > 0 {
		detailTypeNamesExpression := db.SliceExpression(detailTypeNames)

		stmt := jet.
			SELECT(
				jet.DISTINCT(tbDetail.DetailType),
			).
			FROM(
				tbDetail,
			).
			WHERE(
				tbDetail.AccountGroupID.EQ(jet.String(accountGroupID)).
					AND(tbDetail.Type.EQ(jet.String(incomeExpenseType))).
					AND(tbDetail.DetailType.IN(detailTypeNamesExpression...)),
			)

		if err := stmt.QueryContext(ctx, r.db, &query); err != nil {
			return nil, err
		}
	}

	return query, nil
}

// QueryDetails queries income/expense detail by id and account group id.
func (r *incomeExpenseDetailRepository) QueryDetails(ctx context.Context, accountGroupID string, id string) (*IncomeExpenseDetail, error) {
	var query struct {
		ID                   string     `db:"id"`
		IncomeExpenseDate    *time.Time `db:"income_expense_date"`
		Amount               float64    `db:"amount"`
		Type                 string     `db:"type"`
		DetailType           string     `db:"detail_type"`
		PaymentType          string     `db:"payment_type"`
		BankID               *string    `db:"bank_id"`
		BankShortName        *string    `db:"bank_short_name"`
		BankCardID           *string    `db:"bank_card_id"`
		BankCardName         *string    `db:"bank_card_name"`
		BankCardEnabled      *bool      `db:"bank_card_enabled"`
		BankCardIsCreditCard *bool      `db:"bank_card_is_credit_card"`
		Details              string     `db:"details"`
		Remark               *string    `db:"remark"`
		Latitude             *float64   `db:"latitude"`
		Longitude            *float64   `db:"longitude"`
		FormattedAddress     *string    `db:"formatted_address"`
		Address              *string    `db:"address"`
		Province             *string    `db:"province"`
		City                 *string    `db:"city"`
		District             *string    `db:"district"`
		CreatedByStr         *string    `db:"created_by_str"`
		UpdatedByStr         *string    `db:"updated_by_str"`
		DateCreated          *time.Time `db:"date_created"`
		LastUpdated          *time.Time `db:"last_updated"`
		CreatedBy            *UserBasis
		UpdatedBy            *UserBasis
	}

	stmt := jet.
		SELECT(
			tbDetail.ID.AS("id"),
			tbDetail.IncomeExpenseDate.AS("income_expense_date"),
			tbDetail.Amount.AS("amount"),
			tbDetail.Type.AS("type"),
			tbDetail.DetailType.AS("detail_type"),
			tbDetail.PaymentType.AS("payment_type"),
			tbBank.ID.AS("bank_id"),
			tbBank.ShortName.AS("bank_short_name"),
			tbBankCard.ID.AS("bank_card_id"),
			tbBankCard.Name.AS("bank_card_name"),
			tbBankCard.Enabled.AS("bank_card_enabled"),
			tbDetail.Details.AS("details"),
			tbDetail.Remark.AS("remark"),
			tbDetail.Latitude.AS("latitude"),
			tbDetail.Longitude.AS("longitude"),
			tbDetail.Address.AS("address"),
			tbDetail.FormattedAddress.AS("formatted_address"),
			tbDetail.Province.AS("province"),
			tbDetail.City.AS("city"),
			tbDetail.District.AS("district"),
			tbDetail.CreatedBy.AS("created_by_str"),
			tbDetail.UpdatedBy.AS("updated_by_str"),
			tbDetail.DateCreated.AS("date_created"),
			tbDetail.LastUpdated.AS("last_updated"),
		).
		FROM(
			tbDetail.
				LEFT_JOIN(tbBankCard, tbBankCard.ID.EQ(tbDetail.BankCardID)).
				LEFT_JOIN(tbBank, tbBank.ID.EQ(tbBankCard.BankID)),
		).
		WHERE(
			tbDetail.AccountGroupID.EQ(jet.String(accountGroupID)).
				AND(tbDetail.ID.EQ(jet.String(id))),
		)

	if err := stmt.QueryContext(ctx, r.db, &query); err != nil {
		return nil, err
	}

	return (*IncomeExpenseDetail)(&query), nil
}

// QueryCoordinates queries coordinates by account group id and year.
func (r *incomeExpenseDetailRepository) QueryCoordinates(ctx context.Context, accountGroupID string, year string) ([]FormattedAddressCoordinate, error) {
	var (
		yearInt int64
		err     error
	)

	if util.IsEmptyString(year) {
		yearInt = 0
	} else {
		yearInt, err = util.StringToInt64(year)

		if err != nil {
			return nil, err
		}
	}

	// Locations contain same formatted address but different coordinates which we like to take only one match.
	// https://www.navicat.com/en/company/aboutus/blog/1647-applying-select-distinct-to-one-column-only
	subQuery := jet.
		SELECT(
			jet.MAX(tbDetail.ID),
		).
		FROM(
			tbDetail,
		).
		WHERE(
			tbDetail.AccountGroupID.EQ(jet.String(accountGroupID)).
				AND(tbDetail.Type.EQ(jet.String(constant.IncomeExpenseTypeExpense))).
				AND(tbDetail.Latitude.IS_NOT_NULL()).
				AND(tbDetail.Longitude.IS_NOT_NULL()).
				AND(tbDetail.FormattedAddress.IS_NOT_NULL()).
				AND(tbDetail.FormattedAddress.NOT_EQ(jet.String(""))).
				AND(
					jet.Int(yearInt).EQ(jet.Int(0)).
						OR(jet.EXTRACT(jet.YEAR, tbDetail.IncomeExpenseDate).EQ(jet.Int(yearInt))),
				),
		).
		GROUP_BY(
			tbDetail.FormattedAddress,
		)

	var query []struct {
		Latitude         *float64 `db:"latitude"`
		Longitude        *float64 `db:"longitude"`
		FormattedAddress *string  `db:"formatted_address"`
	}

	stmt := jet.
		SELECT(
			tbDetail.FormattedAddress.AS("formatted_address"),
			tbDetail.Latitude.AS("latitude"),
			tbDetail.Longitude.AS("longitude"),
		).
		FROM(
			tbDetail,
		).
		WHERE(
			tbDetail.ID.IN(subQuery),
		)

	if err = stmt.QueryContext(ctx, r.db, &query); err != nil {
		return nil, err
	}

	result := make([]FormattedAddressCoordinate, 0, len(query))

	for _, item := range query {
		result = append(result, item)
	}

	return result, nil
}

// QueryTotalIncomeExpenseCount queris total income/expense count by account group id.
func (r *incomeExpenseDetailRepository) QueryTotalIncomeExpenseCount(ctx context.Context, accountGroupID string) (int64, error) {
	return db.Count(ctx, r.db, tbDetail, tbDetail.AccountGroupID.EQ(jet.String(accountGroupID)))
}

// QueryTotalIncomeExpenseDays queris total income/expense days count by account group id.
func (r *incomeExpenseDetailRepository) QueryTotalIncomeExpenseDays(ctx context.Context, accountGroupID string) (int64, error) {
	return db.CountExpression(ctx, r.db, jet.DISTINCT(tbDetail.IncomeExpenseDate), tbDetail, tbDetail.AccountGroupID.EQ(jet.String(accountGroupID)))
}

// QueryMonthlySummaryList queries income expense summary based one month.
func (r *incomeExpenseDetailRepository) QueryMonthlySummaryList(ctx context.Context, accountGroupID string, date time.Time) ([]AmountSummary, error) {
	var query []struct {
		Amount      float64 `db:"amount"`
		PaymentType string  `db:"payment_type"`
		Type        string  `db:"type"`
	}

	stmt := jet.
		SELECT(
			jet.COALESCE(jet.SUMf(tbDetail.Amount), jet.Int(0)).AS("amount"),
			tbDetail.PaymentType.AS("payment_type"),
			tbDetail.Type.AS("type"),
		).
		FROM(
			tbDetail,
		).
		WHERE(
			tbDetail.AccountGroupID.EQ(jet.String(accountGroupID)).
				AND(jet.EXTRACT(jet.YEAR, tbDetail.IncomeExpenseDate).EQ(jet.Int(int64(date.Year())))).
				AND(jet.EXTRACT(jet.MONTH, tbDetail.IncomeExpenseDate).EQ(jet.Int(int64(date.Month())))),
		).
		GROUP_BY(
			tbDetail.PaymentType,
			tbDetail.Type,
		)

	if err := stmt.QueryContext(ctx, r.db, &query); err != nil {
		return nil, err
	}

	result := make([]AmountSummary, 0, len(query))

	for _, item := range query {
		result = append(result, item)
	}

	return result, nil
}

// QueryDailySummaryList queries income expense summary based one single day.
func (r *incomeExpenseDetailRepository) QueryDailySummaryList(ctx context.Context, accountGroupID string, uniqueDates []time.Time) ([]DatedAmountSummary, error) {
	var query []struct {
		IncomeExpenseDate time.Time `db:"income_expenseDate"`
		Amount            float64   `db:"amount"`
		PaymentType       string    `db:"payment_type"`
		Type              string    `db:"type"`
	}

	if len(uniqueDates) > 0 {
		// With jet.TimeT the generated sql is not what I want.
		// That's why jet.TimestampT is needed.
		uniqueDatesExpr := db.TimestampsExpr(uniqueDates)

		stmt := jet.
			SELECT(
				tbDetail.IncomeExpenseDate.AS("income_expense_date"),
				jet.COALESCE(jet.SUMf(tbDetail.Amount), jet.Int(0)).AS("amount"),
				tbDetail.PaymentType.AS("payment_type"),
				tbDetail.Type.AS("type"),
			).
			FROM(
				tbDetail,
			).
			WHERE(
				tbDetail.AccountGroupID.EQ(jet.String(accountGroupID)).
					AND(tbDetail.IncomeExpenseDate.IN(uniqueDatesExpr...)),
			).
			GROUP_BY(
				tbDetail.IncomeExpenseDate,
				tbDetail.PaymentType,
				tbDetail.Type,
			)

		if err := stmt.QueryContext(ctx, r.db, &query); err != nil {
			return nil, err
		}
	}

	result := make([]DatedAmountSummary, 0, len(query))

	for _, item := range query {
		result = append(result, item)
	}

	return result, nil
}

// QueryExistingDistinctYears queries all distinct income/expense years.
func (r *incomeExpenseDetailRepository) QueryExistingDistinctYears(ctx context.Context, accountGroupID string) ([]int, error) {
	query := []int{}

	stmt := jet.
		SELECT(
			jet.DISTINCT(jet.EXTRACT(jet.YEAR, tbDetail.IncomeExpenseDate)),
		).
		FROM(
			tbDetail,
		).
		WHERE(
			tbDetail.AccountGroupID.EQ(jet.String(accountGroupID)),
		)

	err := stmt.QueryContext(ctx, r.db, &query)

	return query, err
}

// QueryTopRank queries the top income/expense order by amount based on type and payment type in particular year and month.
func (r *incomeExpenseDetailRepository) QueryTopRank(ctx context.Context, accountGroupID string, pageSize int64, pageIndex int64, incomeExpenseType string, paymentType string, year int, month int) ([]IncomeExpenseDetail, error) {
	var query []struct {
		ID                   string     `db:"id"`
		IncomeExpenseDate    *time.Time `db:"income_expense_date"`
		Amount               float64    `db:"amount"`
		Type                 string     `db:"type"`
		DetailType           string     `db:"detail_type"`
		PaymentType          string     `db:"payment_type"`
		BankID               *string    `db:"bank_id"`
		BankShortName        *string    `db:"bank_short_name"`
		BankCardID           *string    `db:"bank_card_id"`
		BankCardName         *string    `db:"bank_card_name"`
		BankCardEnabled      *bool      `db:"bank_card_enabled"`
		BankCardIsCreditCard *bool      `db:"bank_card_is_credit_card"`
		Details              string     `db:"details"`
		Remark               *string    `db:"remark"`
		Latitude             *float64   `db:"latitude"`
		Longitude            *float64   `db:"longitude"`
		FormattedAddress     *string    `db:"formatted_address"`
		Address              *string    `db:"address"`
		Province             *string    `db:"province"`
		City                 *string    `db:"city"`
		District             *string    `db:"district"`
		CreatedByStr         *string    `db:"created_by_str"`
		UpdatedByStr         *string    `db:"updated_by_str"`
		DateCreated          *time.Time `db:"date_created"`
		LastUpdated          *time.Time `db:"last_updated"`
		CreatedBy            *UserBasis
		UpdatedBy            *UserBasis
	}

	stmt := jet.
		SELECT(
			tbDetail.ID.AS("id"),
			tbDetail.IncomeExpenseDate.AS("income_expense_date"),
			tbDetail.Amount.AS("amount"),
			tbDetail.Type.AS("type"),
			tbDetail.DetailType.AS("detail_type"),
			tbDetail.PaymentType.AS("payment_type"),
			tbBank.ID.AS("bank_id"),
			tbBank.ShortName.AS("bank_short_name"),
			tbBankCard.ID.AS("bank_card_id"),
			tbBankCard.Name.AS("bank_card_name"),
			tbBankCard.IsCreditCard.AS("bank_card_is_credit_card"),
			tbDetail.Details.AS("details"),
		).
		FROM(
			tbDetail.
				INNER_JOIN(tbDetailType, tbDetailType.Name.EQ(tbDetail.DetailType)).
				LEFT_JOIN(tbBankCard, tbBankCard.ID.EQ(tbDetail.BankCardID)).
				LEFT_JOIN(tbBank, tbBank.ID.EQ(tbBankCard.BankID)),
		).
		WHERE(
			tbDetailType.AnalysisIncluded.IS_TRUE().
				AND(tbDetailType.AccountGroupID.EQ(jet.String(accountGroupID))).
				AND(tbDetail.AccountGroupID.EQ(jet.String(accountGroupID))).
				AND(tbDetail.Type.EQ(jet.String(incomeExpenseType))).
				AND(
					jet.String(paymentType).EQ(jet.String("")).
						OR(tbDetail.PaymentType.EQ(jet.String(paymentType))),
				).
				AND(
					jet.Int32(int32(year)).EQ(jet.Int(0)).
						OR(jet.EXTRACT(jet.YEAR, tbDetail.IncomeExpenseDate).EQ(jet.Int(int64(year)))),
				).
				AND(
					jet.Int32(int32(month)).EQ(jet.Int(0)).
						OR(jet.EXTRACT(jet.MONTH, tbDetail.IncomeExpenseDate).EQ(jet.Int(int64(month)))),
				),
		).
		ORDER_BY(
			jet.ABSf(tbDetail.Amount).DESC(),
		).
		LIMIT(pageSize).
		OFFSET(pageSize * pageIndex)

	if err := stmt.QueryContext(ctx, r.db, &query); err != nil {
		return nil, err
	}

	result := make([]IncomeExpenseDetail, 0, len(query))

	for _, item := range query {
		result = append(result, item)
	}

	return result, nil
}

// QueryOverallSummary queries overall summary.
func (r *incomeExpenseDetailRepository) QueryOverallSummary(ctx context.Context, accountGroupID string) ([]OverallSummary, error) {
	var query []struct {
		Year        int     `db:"year"`
		Type        string  `db:"type"`
		PaymentType string  `db:"payment_type"`
		Amount      float64 `db:"amount"`
	}

	// By now IncomeExpenseDetail and IncomeExpenseDetailType have no reference field. That's means no foreign key.
	// That's caused join operation takes 250+ms but sub-query takes 10+ms.
	subQuery := jet.
		SELECT(
			jet.Int(1),
		).
		FROM(
			tbDetailType,
		).
		WHERE(
			tbDetailType.AnalysisIncluded.IS_TRUE().
				AND(tbDetailType.AccountGroupID.EQ(jet.String(accountGroupID))).
				AND(tbDetailType.Name.EQ(tbDetail.DetailType)),
		)

	// Queries all SUM data for overall.
	stmt := jet.
		SELECT(
			jet.EXTRACT(jet.YEAR, tbDetail.IncomeExpenseDate).AS("year"),
			tbDetail.Type.AS("type"),
			tbDetail.PaymentType.AS("payment_type"),
			jet.COALESCE(jet.SUMf(tbDetail.Amount), jet.Int(0)).AS("amount"),
		).
		FROM(
			tbDetail,
		).
		WHERE(
			jet.EXISTS(subQuery).
				AND(tbDetail.AccountGroupID.EQ(jet.String(accountGroupID))),
		).
		GROUP_BY(
			jet.EXTRACT(jet.YEAR, tbDetail.IncomeExpenseDate),
			tbDetail.Type,
			tbDetail.PaymentType,
		)

	if err := stmt.QueryContext(ctx, r.db, &query); err != nil {
		return nil, err
	}

	result := make([]OverallSummary, 0, len(query))

	for _, item := range query {
		result = append(result, item)
	}

	return result, nil
}

// QueryAnnualSummary queries annual summary.
func (r *incomeExpenseDetailRepository) QueryAnnualSummary(ctx context.Context, accountGroupID string, years []int) ([]OverallSummary, error) {
	var query []struct {
		Year        int     `db:"year"`
		Type        string  `db:"type"`
		PaymentType string  `db:"payment_type"`
		Amount      float64 `db:"amount"`
	}

	if len(years) > 0 {
		yearsExpression := db.SliceExpression(years)

		// By now IncomeExpenseDetail and IncomeExpenseDetailType have no reference field. That's means no foreign key.
		// That's caused join operation takes 250+ms but sub-query takes 10+ms.
		subQuery := jet.
			SELECT(
				tbDetailType.Name,
			).
			FROM(
				tbDetailType,
			).
			WHERE(
				tbDetailType.AnalysisIncluded.IS_TRUE().
					AND(tbDetailType.AccountGroupID.EQ(jet.String(accountGroupID))),
			)

		// Queries all SUM data for each year.
		stmt := jet.
			SELECT(
				jet.EXTRACT(jet.YEAR, tbDetail.IncomeExpenseDate).AS("year"),
				tbDetail.Type.AS("type"),
				tbDetail.PaymentType.AS("payment_type"),
				jet.COALESCE(jet.SUMf(tbDetail.Amount), jet.Int(0)).AS("amount"),
			).
			FROM(
				tbDetail,
			).
			WHERE(
				tbDetail.DetailType.IN(subQuery).
					AND(tbDetail.AccountGroupID.EQ(jet.String(accountGroupID))).
					AND(jet.EXTRACT(jet.YEAR, tbDetail.IncomeExpenseDate).IN(yearsExpression...)),
			).
			GROUP_BY(
				jet.EXTRACT(jet.YEAR, tbDetail.IncomeExpenseDate),
				tbDetail.Type,
				tbDetail.PaymentType,
			)

		if err := stmt.QueryContext(ctx, r.db, &query); err != nil {
			return nil, err
		}
	}

	result := make([]OverallSummary, 0, len(query))

	for _, item := range query {
		result = append(result, item)
	}

	return result, nil
}

// QuerySummaryByDetailType queries summary by detail type.
func (r *incomeExpenseDetailRepository) QuerySummaryByDetailType(ctx context.Context, accountGroupID string, paymentType string, year int, month int, incomeExpenseType string, detailTypes []string) ([]DetailTypeSummary, error) {
	var query []struct {
		DetailType string  `db:"detail_type"`
		Amount     float64 `db:"amount"`
	}

	if len(detailTypes) > 0 {
		detailTypesExpression := db.SliceExpression(detailTypes)

		stmt := jet.
			SELECT(
				tbDetail.DetailType.AS("detail_type"),
				jet.COALESCE(jet.SUMf(tbDetail.Amount), jet.Int(0)).AS("amount"),
			).
			FROM(
				tbDetail,
			).
			WHERE(
				tbDetail.AccountGroupID.EQ(jet.String(accountGroupID)).
					AND(tbDetail.Type.EQ(jet.String(incomeExpenseType))).
					AND(tbDetail.DetailType.IN(detailTypesExpression...)).
					AND(
						jet.String(paymentType).EQ(jet.String("")).
							OR(tbDetail.PaymentType.EQ(jet.String(paymentType))),
					).
					AND(
						jet.Int64(int64(year)).EQ(jet.Int(0)).
							OR(jet.EXTRACT(jet.YEAR, tbDetail.IncomeExpenseDate).EQ(jet.Int(int64(year)))),
					).
					AND(
						jet.Int64(int64(month)).EQ(jet.Int(0)).
							OR(jet.EXTRACT(jet.MONTH, tbDetail.IncomeExpenseDate).EQ(jet.Int(int64(month)))),
					),
			).
			GROUP_BY(
				tbDetail.DetailType,
			).
			ORDER_BY(
				jet.COALESCE(jet.ABSf(jet.SUMf(tbDetail.Amount)), jet.Int(0)).DESC(),
			)

		if err := stmt.QueryContext(ctx, r.db, &query); err != nil {
			return nil, err
		}
	}

	result := make([]DetailTypeSummary, 0, len(query))

	for _, item := range query {
		result = append(result, item)
	}

	return result, nil
}

// QueryPeriodSummary queries period summary.
func (r *incomeExpenseDetailRepository) QueryPeriodSummary(ctx context.Context, accountGroupID string, incomeExpenseType string, paymentType string, year int, month int) ([]PeriodAmount, error) {
	var query []struct {
		Period int     `db:"period"`
		Amount float64 `db:"amount"`
	}

	selections := jet.ProjectionList{}

	if month > 0 {
		selections = append(selections, jet.EXTRACT(jet.DAY, tbDetail.IncomeExpenseDate).AS("period"))
	} else {
		selections = append(selections, jet.EXTRACT(jet.MONTH, tbDetail.IncomeExpenseDate).AS("period"))
	}

	condition := tbDetailType.AnalysisIncluded.IS_TRUE().
		AND(tbDetail.AccountGroupID.EQ(jet.String(accountGroupID))).
		AND(jet.EXTRACT(jet.YEAR, tbDetail.IncomeExpenseDate).EQ(jet.Int(int64(year)))).
		AND(tbDetail.Type.EQ(jet.String(incomeExpenseType))).
		AND(tbDetail.PaymentType.EQ(jet.String(paymentType)))

	if month > 0 {
		condition = condition.AND(jet.EXTRACT(jet.MONTH, tbDetail.IncomeExpenseDate).EQ(jet.Int(int64(month))))
	}

	stmt := jet.
		SELECT(
			jet.COALESCE(jet.SUMf(tbDetail.Amount), jet.Int(0)).AS("amount"),
			selections...,
		).
		FROM(
			tbDetail.
				INNER_JOIN(tbDetailType, tbDetailType.Name.EQ(tbDetail.DetailType).
					AND(tbDetailType.AccountGroupID.EQ(tbDetail.AccountGroupID))),
		).
		WHERE(condition).
		GROUP_BY(
			jet.EXTRACT(jet.MONTH, tbDetail.IncomeExpenseDate),
		)

	if err := stmt.QueryContext(ctx, r.db, &query); err != nil {
		return nil, err
	}

	result := make([]PeriodAmount, 0, len(query))

	for _, item := range query {
		result = append(result, item)
	}

	return result, nil
}

func (r *incomeExpenseDetailRepository) Add(ctx context.Context, model *dbmodel.IncomeExpenseDetail) error {
	stmt := tbDetail.
		INSERT().
		MODEL(model)

	_, err := stmt.ExecContext(ctx, r.db)

	return err
}

func (r *incomeExpenseDetailRepository) Update(ctx context.Context, accountGroupID string, model *dbmodel.IncomeExpenseDetail) error {
	stmt := tbDetail.
		UPDATE(
			tbDetail.BankCardID,
			tbDetail.Type,
			tbDetail.DetailType,
			tbDetail.PaymentType,
			tbDetail.Details,
			tbDetail.IncomeExpenseDate,
			tbDetail.Amount,
			tbDetail.Remark,
			tbDetail.Province,
			tbDetail.City,
			tbDetail.District,
			tbDetail.Address,
			tbDetail.FormattedAddress,
			tbDetail.Latitude,
			tbDetail.Longitude,
			tbDetail.LastUpdated,
			tbDetail.UpdatedBy,
		).
		MODEL(model).
		WHERE(
			tbDetail.ID.EQ(jet.String(model.ID)).
				AND(tbDetail.AccountGroupID.EQ(jet.String(accountGroupID))),
		)

	_, err := stmt.ExecContext(ctx, r.db)

	return err
}

// UpdateDetailTypeWithTx updates account group detail type based on currenty detail type with transaction.
func (*incomeExpenseDetailRepository) UpdateDetailTypeWithTx(ctx context.Context, tx *sql.Tx, accountGroupID string, incomeExpenseType string, currentDetailType string, newDetailType string) error {
	stmt := tbDetail.
		UPDATE().
		SET(
			tbDetail.DetailType.SET(jet.String(newDetailType)),
		).
		WHERE(
			tbDetail.AccountGroupID.EQ(jet.String(accountGroupID)).
				AND(tbDetail.Type.EQ(jet.String(incomeExpenseType))).
				AND(tbDetail.DetailType.EQ(jet.String(currentDetailType))),
		)

	_, err := stmt.ExecContext(ctx, tx)

	return err
}

func (r *incomeExpenseDetailRepository) BatchDelete(ctx context.Context, accountGroupID string, ids []string) error {
	if len(ids) > 0 {
		idsExpression := db.SliceExpression(ids)

		stmt := tbDetail.
			DELETE().
			WHERE(
				tbDetail.AccountGroupID.EQ(jet.String(accountGroupID)).
					AND(tbDetail.ID.IN(idsExpression...)),
			)

		_, err := stmt.ExecContext(ctx, r.db)

		return err
	}

	return nil
}

// DeleteWithTx deletes all income and expense details by account group id with transaction.
func (*incomeExpenseDetailRepository) DeleteWithTx(ctx context.Context, tx *sql.Tx, accountGroupID string) error {
	stmt := tbDetail.
		DELETE().
		WHERE(
			tbDetail.AccountGroupID.EQ(jet.String(accountGroupID)),
		)

	_, err := stmt.ExecContext(ctx, tx)

	return err
}

// getSharedCondition creates shared condition for where clause. Especially for queryList and queryTotalAmount methods.
func (r *incomeExpenseDetailRepository) getSharedCondition(queryArgument *IncomeExpenseDetailQueryArgument) jet.BoolExpression {
	condition := jet.Bool(true)

	if !util.IsEmptyString(queryArgument.AccountGroupID) {
		condition = condition.AND(tbDetail.AccountGroupID.EQ(jet.String(queryArgument.AccountGroupID)))
	}

	// The start date and end date format is yyyy-MM-dd. No time part. So the default time is 00:00:00.
	// If the start date and end date are both 2019-12-13 00:00:00 and we want to search the data between start and end date
	// we have to change the end date to 2019-12-14 00:00:00.
	// That would satisfy incomeExpenseDate >= startDate && incomeExpenseDate < endDate and match any data with date in 2019-12-13.
	if queryArgument.EndDate != nil {
		queryArgument.EndDate = util.AnyPtr(queryArgument.EndDate.AddDate(0, 0, 1))
	}

	if queryArgument.StartDate != nil {
		condition = condition.AND(tbDetail.IncomeExpenseDate.GT_EQ(jet.DateTimeT(*queryArgument.StartDate)))
	}

	if queryArgument.EndDate != nil {
		condition = condition.AND(tbDetail.IncomeExpenseDate.LT(jet.DateTimeT(*queryArgument.EndDate)))
	}

	if !util.IsEmptyString(queryArgument.Keyword) {
		keywordRegx := regexp.MustCompile(`\s+`)
		keywords := keywordRegx.Split(queryArgument.Keyword, -1)
		var keywordCondition jet.BoolExpression

		for _, keyword := range keywords {
			if !util.IsEmptyString(keyword) {
				keywordsOr := jet.OR(
					tbDetail.Details.LIKE(jet.String("%"+keyword+"%")),
					tbDetail.Remark.LIKE(jet.String("%"+keyword+"%")),
					tbDetail.FormattedAddress.LIKE(jet.String("%"+keyword+"%")),
				)

				if keywordCondition == nil {
					keywordCondition = keywordsOr
				}

				if queryArgument.KeywordsSearchCondition == constant.KeywordsSearchAndCondition {
					// If user wants to search with AND condition for multiple keywords then we just need to wrap each keyword into one OR section.
					// The keyword supports separated by blank string.
					// When it occurs we are splitting the keyword with blank string and gets multiple keywords.
					// The multiple keywords will be used to search within OR condition.
					keywordCondition = keywordCondition.AND(keywordsOr)
				} else {
					// This makes OR as default condition if keywordsSearchCondition is not supplied.
					// If user wants to search with OR condition for multiple keywords then we just need to wrap all of them into one OR section.
					keywordCondition = keywordCondition.OR(keywordsOr)
				}
			}
		}

		condition = condition.AND(keywordCondition)
	}

	if !util.IsEmptyString(queryArgument.Type) {
		condition = condition.AND(tbDetail.Type.EQ(jet.String(queryArgument.Type)))
	}

	if !util.IsEmptyString(queryArgument.DetailType) {
		condition = condition.AND(tbDetail.DetailType.EQ(jet.String(queryArgument.DetailType)))
	}

	if !util.IsEmptyString(queryArgument.PaymentType) {
		condition = condition.AND(tbDetail.PaymentType.EQ(jet.String(queryArgument.PaymentType)))
	}

	if !util.IsEmptyString(queryArgument.BankCardID) {
		condition = condition.AND(tbDetail.BankCardID.EQ(jet.String(queryArgument.BankCardID)))
	}

	return condition
}

type incomeExpenseDetailRepository struct {
	db *sql.DB
}
